package persistence;

import java.awt.Desktop;
import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.SortedMap;
import java.util.TreeMap;

import control.MessageHandler;
import control.NewBookAction;

import domain.Book;
import domain.Copy;
import domain.Library;
import domain.Loan;

import jxl.*;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.Number;
import jxl.write.biff.RowsExceededException;

public class XlsStatistic {
	private String inputFile;
	private String outputFile;
	private WritableCellFormat timesBoldUnderline;
	private WritableCellFormat times;
	private Library lib = Library.getInstance();
	private int status[] = { 0, 0, 0, 0, 0 };
	private int loansStat[] = { 0, 0, 0, 0 };
	private WritableWorkbook workbook;
	private WritableSheet excelSheet;
	private Date fromDate;
	private Date untilDate;
	private WritableSheet loanSheet;
	private DateFormat formatTime;
	private MessageHandler msghandle;
	private File fileIn;
	private File fileOut;

	public XlsStatistic(String inputFile, String outputFile) {
		setInputFile(inputFile, outputFile);
		formatTime = DateFormat.getDateInstance();
	}

	public void setInputFile(String inputFile, String outputFile) {
		this.inputFile = inputFile;
		this.outputFile = outputFile;

	}

	public void setRange(Date from, Date until) {
		this.fromDate = from;
		this.untilDate = until;
	}

	public void write() throws IOException, WriteException {
		fileIn = new File(inputFile);
		fileOut = new File(outputFile);

		// wbSettings.setLocale(new Locale("en", "EN"));
		Workbook workbookIn = null;
		try {
			workbookIn = Workbook.getWorkbook(fileIn);
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		workbook = Workbook.createWorkbook(fileOut, workbookIn);
		excelSheet = workbook.getSheet(workbook.getNumberOfSheets() - 1);
		loanSheet = workbook.getSheet(2);
		addLoans(loanSheet, getLoansBetween(this.fromDate, this.untilDate));

		addStatus(excelSheet);
		addOverview(excelSheet);
		addLoanDuration(excelSheet,
				getLoansBetweenreturned(this.fromDate, this.untilDate));

		workbook.write();
		workbookIn.close();
		workbook.close();
	}

	private void getBookStatus() {
		for (Copy c : lib.getCopies()) {
			switch (c.getCondition()) {
			case NEW:
				status[0]++;
				break;
			case GOOD:
				status[1]++;
				break;
			case DAMAGED:
				status[2]++;
				break;
			case WASTE:
				status[3]++;
				break;
			case LOST:
				status[4]++;
				break;
			}
		}
	}

	private List<Loan> getLoansBetween(Date start, Date end) {
		List<Loan> between = new ArrayList<Loan>();
		for (Loan l : lib.getLoans()) {
			if (l.getPickupDate().getTime().after(start)) {
				if (l.isLent()) {
					between.add(l);
				} else {
					if (l.getReturnDate().getTime().before(end)) {
						between.add(l);
					}
				}
			}
		}
		return between;
	}

	private List<Loan> getLoansBetweenreturned(Date start, Date end) {
		List<Loan> between = new ArrayList<Loan>();
		for (Loan l : lib.getLoans()) {

			if (!l.isLent()) {
				if (l.getReturnDate().getTime().after(start)) {
					between.add(l);
				}
			}
		}
		return between;
	}

	private void addTopAndLower15(WritableSheet sheet,
			HashMap<Book, Integer> books) {
		TreeMap<Integer, List<Book>> sortedMapLower = new TreeMap<Integer, List<Book>>();
		Iterator putter = books.entrySet().iterator();
		int x = 0;
		while (putter.hasNext()) {
			Map.Entry pairs = (Map.Entry) putter.next();
			if (sortedMapLower.containsKey(pairs.getValue())) {
				sortedMapLower.get(pairs.getValue()).add((Book) pairs.getKey());
			} else {
				List<Book> buff = new ArrayList<Book>();
				buff.add((Book) pairs.getKey());
				sortedMapLower.put((Integer) pairs.getValue(), buff);
			}
		}

		Iterator lowerIt = sortedMapLower.entrySet().iterator();
		int row = 0;
		while (lowerIt.hasNext() && row < 10) {

			Map.Entry pairs = (Map.Entry) lowerIt.next();
			try {
				List<Book> buffer = (List<Book>) pairs.getValue();
				for (Book b : buffer) {
					if (row < 10) {
						addLabel(excelSheet, 4, row, b.getName());
						addNumber(excelSheet, 5, row, (Integer) pairs.getKey());
						row++;

					} else {
						break;
					}
				}
			} catch (RowsExceededException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (WriteException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		TreeMap<Integer, List<Book>> sortedMapTop = new TreeMap<Integer, List<Book>>(
				Collections.reverseOrder());
		sortedMapTop.putAll(sortedMapLower);
		Iterator topIt = sortedMapTop.entrySet().iterator();
		row = 0;
		while (topIt.hasNext() && row < 10) {

			Map.Entry pairs = (Map.Entry) topIt.next();
			try {
				List<Book> buffer = (List<Book>) pairs.getValue();
				for (Book b : buffer) {
					if (row < 10) {
						addLabel(excelSheet, 6, row, b.getName());
						addNumber(excelSheet, 7, row, (Integer) pairs.getKey());
						row++;

					} else {
						break;
					}
				}
			} catch (RowsExceededException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (WriteException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	private void addLoans(WritableSheet sheet, List<Loan> loans) {
		HashMap<Book, Integer> books = new HashMap<Book, Integer>();
		for (Book b : lib.getBooks()) {
			books.put(b, 0);
		}
		if (loans.size() > 0) {
			for (Loan l : loans) {
				if (books.containsKey(l.getCopy().getTitle())) {
					int buffer = books.get(l.getCopy().getTitle());
					books.put(l.getCopy().getTitle(), buffer + 1);
				}
			}
		}
		SimpleDateFormat ft = new SimpleDateFormat("dd.MM.yyyy");
		try {
			addLabel(sheet, 1, 0,
					"Ausleihen von " + ft.format(this.fromDate)
							+ " bis " + ft.format(this.untilDate));
		} catch (RowsExceededException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (WriteException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Iterator it = books.entrySet().iterator();
		int row = 1;
		while (it.hasNext()) {
			Map.Entry pairs = (Map.Entry) it.next();
			try {
				addLabel(sheet, 0, row, ((Book) pairs.getKey()).getName());
				addNumber(sheet, 1, row, (Integer) pairs.getValue());
			} catch (RowsExceededException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (WriteException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			row++;
		}
		addTopAndLower15(sheet, books);
	}

	private void addStatus(WritableSheet sheet) throws WriteException,
			RowsExceededException {
		getBookStatus();
		// Write a few number
		for (int i = 0; i < status.length; i++) {
			// First column
			addNumber(sheet, 1, i, status[i]);
		}
	}

	private void addLoanDuration(WritableSheet sheet, List<Loan> loans)
			throws WriteException, RowsExceededException {

		for (Loan l : loans) {
			if (l.getDaysOfLoanDuration() > 30) {
				loansStat[0]++;
			} else if (l.getDaysOfLoanDuration() >= 20
					&& l.getDaysOfLoanDuration() < 30) {
				System.out.println("TEST");
				loansStat[1]++;
			} else if (l.getDaysOfLoanDuration() >= 10
					&& l.getDaysOfLoanDuration() < 20) {
				loansStat[2]++;
			} else if (l.getDaysOfLoanDuration() < 10) {
				loansStat[3]++;
			}

		}

		for (int i = 0; i < loansStat.length; i++) {

			addNumber(sheet, 9, i, loansStat[i]);
		}
	}

	private void addOverview(WritableSheet sheet) throws WriteException,
			RowsExceededException {
		getBookStatus();
		// Write a few number
		addNumber(sheet, 3, 0, lib.getBooks().size());
		addNumber(sheet, 3, 1, lib.getCopies().size());
		addNumber(sheet, 3, 2, lib.getLentOutBooks().size());
		addNumber(sheet, 3, 3, lib.getLoans().size());
		addNumber(sheet, 3, 4, lib.getCustomers().size());

	}

	// private void addCaption(WritableSheet sheet, int column, int row, String
	// s)
	// throws RowsExceededException, WriteException {
	// Label label;
	// label = new Label(column, row, s, timesBoldUnderline);
	// sheet.addCell(label);
	// }

	private void addNumber(WritableSheet sheet, int column, int row,
			Integer integer) throws WriteException, RowsExceededException {
		Number number;
		number = new Number(column, row, integer);
		sheet.addCell(number);
	}

	private void addLabel(WritableSheet sheet, int column, int row, String s)
			throws WriteException, RowsExceededException {
		Label label;
		label = new Label(column, row, s);
		sheet.addCell(label);
	}
	
	public void openXls(){
		try {
			if (fileOut.exists()) {
	 
				if (Desktop.isDesktopSupported()) {
					Desktop.getDesktop().open(fileOut);
				} else {
					System.out.println("Awt Desktop is not supported!");
				}
	 
			} else {
				System.out.println("File does not exist!");
			}
	 
		  } catch (Exception ex) {
			ex.printStackTrace();
		  }
	}

}
